import frappe
import calendar
from datetime import datetime

def execute(filters=None):
    if not filters:
        filters={}

    columns = get_columns()
    data = get_report_data(filters)
    

    return columns, data


def get_columns():
    return [
        {"label": "行号", "fieldname": "idx", "fieldtype": "Int"},
        {"label": "客户名称", "fieldname": "customer_name", "fieldtype": "Data", "width": 105},
        {"label": "终客户", "fieldname": "final_cus", "fieldtype": "Data", "width": 60},
        {"label": "规格", "fieldname": "specification", "fieldtype": "Data", "width": "auto"},
        {"label": "镀种", "fieldname": "plating_type", "fieldtype": "Data", "width": 120},
        # {"label": "收货批", "fieldname": "control_batch", "fieldtype": "Data", "width": 100},
        {"label": "订单数量", "fieldname": "qty", "fieldtype": "Float", "width": 84},
        {"label": "入库数量", "fieldname": "in_qty", "fieldtype": "Float", "width": 84},
        {"label": "可出库", "fieldname": "available_qty", "fieldtype": "Float", "width": 84},
        {"label": "未入库", "fieldname": "remaining_qty", "fieldtype": "Float", "width": 84},
        {"label": "批次", "fieldname": "batch_no", "fieldtype": "Data", "width": 100},
        # {"label": "物料号", "fieldname": "item_code", "fieldtype": "Data", "width": 120},
        {"label": "销售订单", "fieldname": "parent", "fieldtype": "Data", "width": 185},
    ]

def get_report_data(filters_t):
    # 先取得 docstatus = 1 的 Sales Order
    filters={
        "status": ['not in',['Closed','Cancelled']],
        "docstatus":1
    }
    cus=filters_t.get('customer_name')
    if cus:
        filters['customer']=cus

    valid_so = frappe.db.get_all(
        "Sales Order",
        filters=filters,
        pluck="name"
    )
    # 取所有 SO 的 customer + customer_name
    so_info_map = {
        so.name: so.customer_name
        for so in frappe.get_all(
            "Sales Order",
            filters={"name": ["in", valid_so]},
            fields=["name", "customer_name"]
        )
    }

    if not valid_so:
        return []

    items = frappe.db.get_all(
        "Sales Order Item",
        filters={
            "parent": ["in", valid_so],
            "custom_line_status": ["not in", ["HARD_CLOSE", "CANCEL","CLOSE"]],
            "docstatus": 1
        },
        fields=[
            "name",
            "parent",
            "idx",
            "item_code",
            "custom_客户料号",
            "custom_终客户",
            "custom_规格",
            "custom_批次",
            "custom_control_batch",
            "custom_客户镀种",
            "custom_line_status",
            "qty",
            "custom_入库数量",
            "custom_可出库数量",
        ],
        order_by="idx asc"
    )

    data = []
    for i in items:
        in_qty = i.custom_入库数量 or 0
        remaining_qty = (i.qty or 0) - in_qty

        row = {
            "idx": i.idx,
            "parent": i.parent,  # 添加销售订单号
            "final_cus": i.custom_终客户,  # 添加销售订单号
            "name": i.name,  # 添加 Sales Order Item 的 name
            "item_code": i.item_code,
            "customer_item_code": i.custom_客户料号,
            "customer_name": so_info_map.get(i.parent, ""),
            "specification": i.custom_规格,
            "batch_no": i.custom_批次 or "",
            "control_batch": i.custom_control_batch or "",
            "plating_type": i.custom_客户镀种 or "",
            "qty": i.qty or 0,
            "in_qty": in_qty,
            "available_qty": i.custom_可出库数量 or 0,
            "remaining_qty": remaining_qty,
            "current_receive_qty": "",  # 改为空字符串，用于输入框
            "line": "",  # 改为空字符串，用于输入框
        }

        data.append(row)

    return data